const models = require("../db/mysqldb/index");
const { Op } = require("sequelize");
const { sequelize } = require("sequelize");
const moment = require("moment");

class DataSystemUserModel {
  /**
   * @description 新增用户
   * @param {string} user_id
   * @returns
   */
  static async add({
    dept_id,
    user_name,
    password,
    user_number,
    nick_name,
    user_type,
    email,
    phonenumber,
    sex,
    avatar,
    remark,
    create_by,
    postIds,
    roleIds,
  }) {
    return await models.sequelize.transaction(function (t) {
      let userInfo = {};
      return models.sys_user
        .create(
          {
            dept_id,
            user_name,
            password,
            user_number,
            nick_name,
            user_type,
            email,
            phonenumber,
            sex,
            avatar,
            remark,
            create_by,
            create_date: Date.now(),
          },
          { transaction: t }
        )
        .then((user) => {
          userInfo = user;
          if (Array.isArray(postIds) && postIds.length) {
            let postes = postIds.map((item) => {
              return {
                post_id: item,
                user_id: userInfo.user_id,
              };
            });
            return models.sys_user_post.bulkCreate(postes, { transaction: t });
          }
        })
        .then(() => {
          if (Array.isArray(roleIds) && roleIds.length) {
            let roles = roleIds.map((item) => {
              return {
                role_id: item,
                user_id: userInfo.user_id,
              };
            });
            return models.sys_user_role.bulkCreate(roles, { transaction: t });
          }
        })
        .catch((err) => {
          console.log(err.message);
          return t.rollback();
        });
    });
  }
  /**
   * @description 更新用户
   * @param {string} user_id
   * @returns
   */
  static async updateUser({
    dept_id,
    user_name,
    user_number,
    nick_name,
    user_type,
    email,
    phonenumber,
    sex,
    avatar,
    remark,
    update_by,
    update_date,
    user_id,
    postIds,
    roleIds,
  }) {
    return await models.sequelize.transaction(function (t) {
      return Promise.all([
        models.sys_user.update(
          {
            dept_id,
            user_name,
            user_number,
            nick_name,
            user_type,
            email,
            phonenumber,
            sex,
            avatar,
            remark,
            update_by,
            update_date,
          },
          { transaction: t, where: { user_id } }
        ),
        models.sys_user_post.destroy({
          where: { user_id },
          transaction: t,
        }),
        models.sys_user_role.destroy({
          where: { user_id },
          transaction: t,
        }),
      ])
        .then(() => {
          if (Array.isArray(postIds) && postIds.length) {
            let postes = postIds.map((item) => {
              return {
                post_id: item,
                user_id,
              };
            });
            return models.sys_user_post.bulkCreate(postes, { transaction: t });
          }
        })
        .then(() => {
          if (Array.isArray(roleIds) && roleIds.length) {
            let roles = roleIds.map((item) => {
              return {
                role_id: item,
                user_id,
              };
            });
            return models.sys_user_role.bulkCreate(roles, { transaction: t });
          }
        })
        .catch((err) => {
          console.log(err.message);
          return t.rollback();
        });
    });
  }

  /**
   * @description 通过 user_id 查找数据，不包含删除数据
   * @param {string} user_id
   * @returns
   */
  static async findUser({ user_number, phonenumber }) {
    let sql = `SELECT user_id FROM sys_user WHERE (user_number = '${user_number}' or phonenumber = '${phonenumber}') and del_flag = '0' and status = '1'`;
    return await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
  }
  /**
   * @description 通过 user_name 查找数据，不包含删除数据
   * @param {string} user_name
   * @returns
   */
  static async findBySystemUserName({ user_name }) {
    return await models.sys_user.findOne({
      where: {
        user_name,
        del_flag: "0",
      },
    });
  }
  /**
   * @description 通过 user_name 查找数据，不包含删除数据
   * @param {string} user_name
   * @returns
   */
  static async findBySystemUserNumber({ user_number }) {
    return await models.sys_user.findOne({
      where: {
        user_number,
        del_flag: "0",
      },
    });
  }
  /**
   * @description 通过 user_id 查找数据，不包含删除数据
   * @param {string} user_id
   * @returns
   */
  static async findBySystemUserId(user_id) {
    let sql = `SELECT user_id,dept_id,user_name,user_number,nick_name,user_type,email,phonenumber,sex,avatar,remark FROM sys_user WHERE user_id = ${user_id} and del_flag = 0 and status = 1`;
    return await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
  }
  /**
   * @description 通过 user_number 查找数据，不包含删除数据
   * @param {string} user_number
   * @param {Number} user_id
   * @param {string} user_name
   * @returns
   */
  static async findOneNeUserId({
    user_number,
    user_id,
    user_name,
    phonenumber,
  }) {
    let sql = `SELECT user_id FROM sys_user WHERE user_id <> ${user_id} and del_flag = '0' and (user_number = '${user_number}' or user_name = '${user_name}' and phonenumber = '${phonenumber}')`;
    return await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
  }

  /**
   * @description 通过 user_number 查找
   * @param {string} user_number
   * @returns
   */
  static async findOne({ user_number }) {
    return await models.sys_user.findOne({
      where: {
        user_number,
        del_flag: "0",
      },
    });
  }

  /**
   * @description 通过 user_number id 查找数据，不包含删除数据
   * @param {string} user_number
   * @param {string} user_id
   * @returns
   */
  static async findBySystemUserNameNeId(user_number, user_id) {
    return await models.sys_user.findOne({
      where: {
        user_number,
        del_flag: "0",
        user_id: {
          [Op.ne]: user_id,
        },
      },
    });
  }

  /**
   * @description create
   * @param {String} user_name
   * @param {String} dept_id
   * @param {String} user_number
   * @param {String} nick_name
   * @param {String} user_type
   * @param {String} email
   * @param {String} phonenumber
   * @param {String} sex
   * @param {String} avatar
   * @param {String} status
   * @param {String} del_flag
   * @param {String} login_ip
   * @param {String} login_date
   * @param {String} remark
   * @param {String} op_user_id
   * @returns
   */
  static async create(
    user_name,
    dept_id,
    user_number,
    nick_name,
    user_type,
    email,
    phonenumber,
    sex,
    avatar,
    status,
    del_flag,
    login_ip,
    login_date,
    remark,
    op_user_id
  ) {
    return await models.sys_user.create({
      user_name,
      dept_id,
      user_number,
      nick_name,
      user_type,
      email,
      phonenumber,
      sex,
      avatar,
      status,
      del_flag,
      login_ip,
      login_date,
      remark,
      create_by: op_user_id,
      create_date: Date.now(),
    });
  }
  /**
   * @param {String} user_name
   * @param {String} dept_id
   * @param {String} user_number
   * @param {String} nick_name
   * @param {String} user_type
   * @param {String} email
   * @param {String} phonenumber
   * @param {String} sex
   * @param {String} avatar
   * @param {String} status
   * @param {String} del_flag
   * @param {String} login_ip
   * @param {String} login_date
   * @param {String} remark
   * @param {String} op_user_id
   * @param {Number} user_id
   * @returns
   */
  static async update(
    user_name,
    dept_id,
    user_number,
    nick_name,
    user_type,
    email,
    phonenumber,
    sex,
    avatar,
    status,
    del_flag,
    login_ip,
    login_date,
    remark,
    op_user_id,
    user_id
  ) {
    return await models.sys_user.update(
      {
        user_name,
        dept_id,
        user_number,
        nick_name,
        user_type,
        email,
        phonenumber,
        sex,
        avatar,
        status,
        del_flag,
        login_ip,
        login_date,
        remark,
        update_by: op_user_id,
        update_time: Date.now(),
      },
      {
        where: { user_id },
      }
    );
  }
  /**
   * @param {String} login_ip
   * @param {Number} user_id
   * @returns
   */
  static async updateLoginInfo(login_ip, user_id) {
    let time = moment().format("YYYY-MM-DD HH:mm:ss");
    return await models.sys_user.update(
      {
        login_ip,
        login_date: time,
        update_by: user_id,
        update_time: time,
      },
      {
        where: { user_id },
      }
    );
  }
  /**
   * @description 删除
   * @param {*} op_user_id
   * @param {*} user_id
   * @returns
   */
  static async delete(op_user_id, user_id) {
    return await models.sys_user.update(
      {
        del_flag: "2",
        update_by: op_user_id,
      },
      {
        where: { user_id },
      }
    );
  }
  /**
   * @description 批量删除
   * @param {*} op_user_id
   * @param {*} ids
   * @returns
   */
  static async deleteByIds(op_user_id, ids) {
    return await models.sys_user.update(
      {
        del_flag: "2",
        update_by: op_user_id,
      },
      {
        where: {
          user_id: {
            [Op.in]: ids,
          },
        },
      }
    );
  }
  /**
   * @description 获取用户信息 分页
   * @param {String} user_name
   * @param {String} user_number
   * @param {String} status
   * @param {String} limit
   * @param {String} size
   * @returns
   */
  static async list({
    limit,
    size,
    user_name,
    user_number,
    status,
    phonenumber,
    data_scope,
    dept_id,
  }) {
    let querySql = "";
    if (user_name) {
      querySql += ` and su.user_name like '%${user_name}%'`;
    }
    if (user_number) {
      querySql += ` and su.user_number like '%${user_number}%'`;
    }
    if (status) {
      querySql += ` and su.status = '${status}'`;
    }
    if (phonenumber) {
      querySql += ` and su.phonenumber like '%${phonenumber}%'`;
    }
    // 查询优化 部门子节点也要查
    if (dept_id) {
      querySql += ` and su.dept_id = '${dept_id}' `;
    }
    let sql = `SELECT
        su.user_id,
        su.user_name,
        su.user_number,
        su.phonenumber,
        su.dept_id,
        su.nick_name,
        su.email,
        su.sex,
        su.avatar,
        su.status,
        sd.dept_name,
        su.remark,
        su.create_time
      FROM
        sys_user su left join sys_dept sd on su.dept_id = sd.dept_id
        WHERE su.del_flag = '0' ${querySql} ${data_scope}
      ORDER BY
      sd.update_time desc,sd.create_time desc limit ${(limit - 1) * size}, ${size}`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    const count = await models.sequelize.query(
      `SELECT count(1) as count from sys_user su WHERE del_flag = '0' ${querySql} ${data_scope};`,
      { replacements: ["active"], type: models.sequelize.QueryTypes.SELECT }
    );
    return {
      result,
      count,
    };
  }

  /**
   * @description 获取所有的用户的id
   * @returns
   */
  static async getAllUserId() {
    let sql = `SELECT
        user_id
      FROM
        sys_user`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    return {
      result,
    };
  }

  /**
   * @description 根据权限获取部门id 根据部门信息获取用户id
   * @param {String} role_id
   * @returns
   */
  static async getUserIdByRoleId(role_id) {
    let sql = `
    SELECT user_id 
    from sys_user 
    WHERE dept_id in (select dept_id from sys_role_dept WHERE role_id = ${role_id})`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    return {
      result,
    };
  }

  /**
   * @description 根据部门id获取用户id
   * @param {String} dept_id
   * @returns
   */
  static async getUserIdByDeptId(dept_id) {
    let sql = `
    SELECT user_id 
    from sys_user 
    WHERE dept_id = ${dept_id}`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    return {
      result,
    };
  }

  /**
   * @description 获取当前部门及子部门下所有的用户id
   * @param {String} dept_id
   * @returns
   */
  static async getUserIdByDeptTreeId(dept_id) {
    let sql = `
    WITH recursive tree AS (
      SELECT
        dep.dept_id,
        dep.dept_name,
        dep.parent_id 
      FROM
        sys_dept dep 
      WHERE
        dep.dept_id = ${dept_id} UNION ALL
      SELECT
        dep.dept_id,
        dep.dept_name,
        dep.parent_id 
      FROM
        sys_dept dep
        INNER JOIN tree ON dep.parent_id = tree.dept_id 
      ) SELECT
      dept_id,dept_name,parent_id
    FROM
      tree;`;
    const result = await models.sequelize.query(sql, {
      replacements: ["active"],
      type: models.sequelize.QueryTypes.SELECT,
    });
    return {
      result,
    };
  }
  /**
   * @description 删除
   * @param {*} op_user_id
   * @param {*} user_id
   * @returns
   */
  static async delete({ op_user_id, user_id }) {
    return await models.sys_user.update(
      {
        del_flag: "2",
        update_time: Date.now(),
        update_by: op_user_id,
      },
      {
        where: { user_id },
      }
    );
  }
}
module.exports = {
  DataSystemUserModel,
};
